﻿using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Linq;
using System.Reflection;
using System.Text;


namespace ANZ_AIM_DAL
{

    public class CommandHelper
    {
        /// <summary>
        /// Build Insert Command.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="obj">Object to insert.</param>
        /// <param name="mappingDict">A dictionary mapping object Field(Key) with DB Field(Value).</param>
        /// <returns>String: INSERT INTO TableName ([F1], [F2], [F3]) VALUES (@f1, @f2, @f3).</returns>
        public static string BuildInsertCommand(string tableName, Dictionary<string, string> mappingDict)
        {
            try
            {
                string command = string.Empty;
                string insertCmd = string.Empty; // [F1], [F2], [F3]
                string valueCmd = string.Empty;
                string dbFieldName = string.Empty;

                foreach (var field in mappingDict)
                {
                    if (mappingDict.TryGetValue(field.Key, out dbFieldName))
                    {
                        insertCmd += string.Format("[{0}], ", dbFieldName);
                        valueCmd += string.Format("@{0}, ", field.Key);
                    }
                }
                insertCmd = insertCmd.Trim().TrimEnd(',');
                valueCmd = valueCmd.Trim().TrimEnd(',');
                command = string.Format("INSERT INTO [{0}] ({1}) VALUES ({2})", tableName, insertCmd, valueCmd);

                return command;
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        public static OleDbParameter[] BuildParameters<T>(T obj, List<KeyValuePair<string, string>> mappingFields)
        {
            OleDbParameter[] paramArr = new OleDbParameter[mappingFields.Count];
            string paramName = string.Empty;
            object tempValue;

            for (int i = 0; i < mappingFields.Count(); i++)
            {
                paramName = mappingFields.ElementAt(i).Key;
                var item = obj.GetType().GetProperty(paramName);
                tempValue = item.GetValue(obj, null);
                paramArr[i] = new OleDbParameter(string.Format("@{0}", paramName), (tempValue ?? DBNull.Value));
            }

            return paramArr;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tableName"></param>
        /// <param name="mappingDict"></param>
        /// <returns>String: UPDATE TableName SET [F3] = @f3, [F4] = @f4 WHERE [F1] = @f1 AND [F2] = @f2</returns>
        public static string BuildUpdateCommand(string tableName, Dictionary<string, string> mappingDict, Dictionary<string, string> conditionDict)
        {
            try
            {
                string command = string.Empty;
                string updateCmd = string.Empty; // [F1], [F2], [F3]
                string dbFieldName = string.Empty;
                string whereCmd = string.Empty;

                foreach (var field in mappingDict)
                {
                    if (mappingDict.TryGetValue(field.Key, out dbFieldName))
                    {
                        updateCmd += string.Format("[{0}] = @{1}, ", dbFieldName, field.Key);
                    }
                }

                foreach (var field in conditionDict)
                {
                    if (conditionDict.TryGetValue(field.Key, out dbFieldName))
                    {
                        whereCmd += string.Format("[{0}] = @{1} AND ", dbFieldName, field.Key);
                    }
                }

                updateCmd = updateCmd.Trim().TrimEnd(',');
                whereCmd = whereCmd.Trim().TrimEnd('D').TrimEnd('N').TrimEnd('A').Trim();
                command = string.Format("UPDATE [{0}] SET {1} WHERE {2}", tableName, updateCmd, whereCmd);

                return command;
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        /// <summary>
        /// Build Select Command.
        /// </summary>
        /// <typeparam name="T">Object to select.</typeparam>
        /// <param name="obj"></param>
        /// <param name="fieldDict"></param>
        /// <returns></returns>
        //public static string BuildSelectCommand<T>(T obj, Dictionary<string, string> fieldDict)
        //{
        //    try
        //    {
        //        string result = string.Empty;
        //        string selectCmd = string.Empty;
        //        string tableName = string.Empty;

        //        Dictionary<string, object> conditionDict = BuildConditionDictionary(obj);
        //        string whereCmd = BuildWhereString(fieldDict, conditionDict);
        //        AppParam.Instance.DBTableDict.TryGetValue(obj.GetType().Name, out tableName);

        //        string name = string.Empty;
        //        foreach (var item in fieldDict)
        //        {
        //            fieldDict.TryGetValue(item.Key, out name);
        //            selectCmd += string.Format("[{0}], ", name);
        //        }
        //        selectCmd = selectCmd.Trim().TrimEnd(',');
        //        result = string.Format("SELECT {0} FROM {1} WHERE {2}", selectCmd, tableName, whereCmd);

        //        return result;
        //    }
        //    catch (Exception ex)
        //    {
        //        throw;
        //    }
        //}

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="obj"></param>
        /// <param name="dictionary"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static string BuildInsertCommand<T>(T obj, Dictionary<string, string> dictionary, out OleDbParameter[] parameters)
        {
            string result = string.Empty;
            string tableName = string.Empty;
            string insertClause = string.Empty;
            string valueClause = string.Empty;

            AppParam.Instance.DBTableDict.TryGetValue(obj.GetType().Name, out tableName);
            Dictionary<string, object> conditionDict = BuildConditionDictionary(obj);
            string tempDbFieldName = string.Empty;
            List<string> fieldNames = new List<string>();
            foreach (var o in conditionDict)
            {
                if (!dictionary.ContainsKey(o.Key))
                {
                    continue;
                }
                dictionary.TryGetValue(o.Key, out tempDbFieldName);
                insertClause += string.Format("[{0}], ", tempDbFieldName);
                valueClause += string.Format("@{0}, ", o.Key);
                fieldNames.Add(o.Key);
            }

            parameters = new OleDbParameter[fieldNames.Count];
            object tempValue;
            for (int i = 0; i < fieldNames.Count; i++)
            {
                tempValue = obj.GetType().GetProperty(fieldNames[i]).GetValue(obj, null);
                parameters[i] = new OleDbParameter(string.Format(@"{0}", fieldNames[i]), tempValue);
            }

            insertClause = insertClause.Trim().TrimEnd(',').Trim();
            valueClause = valueClause.Trim().TrimEnd(',').Trim();
            result = string.Format("INSERT INTO [{0}] ({1}) VALUES ({2})", tableName, insertClause, valueClause);
            return result;
        }

        public static string BuildInsertCommand<T>(T obj, Dictionary<string, string> dictionary, string tableName, out OleDbParameter[] parameters)
        {
            string result = string.Empty;
            string insertClause = string.Empty;
            string valueClause = string.Empty;

            Dictionary<string, object> conditionDict = BuildConditionDictionary(obj);
            string tempDbFieldName = string.Empty;
            List<string> fieldNames = new List<string>();
            foreach (var o in conditionDict)
            {
                if (!dictionary.ContainsKey(o.Key))
                {
                    continue;
                }
                dictionary.TryGetValue(o.Key, out tempDbFieldName);
                insertClause += string.Format("[{0}], ", tempDbFieldName);
                valueClause += string.Format("@{0}, ", o.Key);
                fieldNames.Add(o.Key);
            }

            parameters = new OleDbParameter[fieldNames.Count];
            object tempValue;
            for (int i = 0; i < fieldNames.Count; i++)
            {
                tempValue = obj.GetType().GetProperty(fieldNames[i]).GetValue(obj, null);
                parameters[i] = new OleDbParameter(string.Format(@"{0}", fieldNames[i]), tempValue);
            }

            insertClause = insertClause.Trim().TrimEnd(',').Trim();
            valueClause = valueClause.Trim().TrimEnd(',').Trim();
            result = string.Format("INSERT INTO [{0}] ({1}) VALUES ({2})", tableName, insertClause, valueClause);
            return result;
        }


        /// <summary>
        /// Build List of Fields to select.
        /// </summary>
        /// <param name="dictionary"></param>
        /// <returns>String: [F1], [F2], [F3], [F4]</returns>
        public static string BuildSelectAllFieldCommand(Dictionary<string, string> dictionary)
        {
            try
            {
                string result = string.Empty;
                if (dictionary == null || !dictionary.Any())
                {
                    return result;
                }

                foreach (var item in dictionary)
                {
                    result += string.Format("[{0}], ", item.Value);
                }

                result = result.Trim().TrimEnd(',');

                return result;
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="obj"></param>
        /// <param name="dictionary"></param>
        /// <param name="parameters"></param>
        /// <returns>String: COUNT([F1]) FROM TableName WHERE [F1] = @f1 AND [F2] = @f2.</returns>
        public static string BuildCountCommand<T>(T obj, Dictionary<string, string> dictionary, out OleDbParameter[] parameters)
        {
            try
            {
                string result = string.Empty;
                string whereString = string.Empty;
                string tableName = string.Empty;
                AppParam.Instance.DBTableDict.TryGetValue(obj.GetType().Name, out tableName);

                Dictionary<string, object> conditionDict = BuildConditionDictionary(obj, false);
                string tempDbFieldName = string.Empty;
                List<string> fieldNames = new List<string>();
                foreach (var o in conditionDict)
                {
                    if (!dictionary.ContainsKey(o.Key))
                    {
                        continue;
                    }
                    dictionary.TryGetValue(o.Key, out tempDbFieldName);
                    whereString += string.Format("[{0}] = @{1} AND ", tempDbFieldName, o.Key);
                    fieldNames.Add(o.Key);
                }

                parameters = new OleDbParameter[fieldNames.Count];
                object tempValue;
                for (int i = 0; i < fieldNames.Count; i++)
                {
                    tempValue = obj.GetType().GetProperty(fieldNames[i]).GetValue(obj, null);
                    parameters[i] = new OleDbParameter(string.Format(@"{0}", fieldNames[i]), tempValue);
                }

                whereString = whereString.Trim().TrimEnd('D').TrimEnd('N').TrimEnd('A').TrimEnd();
                result = string.Format("SELECT COUNT ({0}) FROM {1} WHERE {2}", tempDbFieldName, tableName, whereString);

                return result;
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="obj"></param>
        /// <param name="fieldDict"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static string BuildSelectCommand<T>(T obj, Dictionary<string, string> fieldDict, out OleDbParameter[] parameters)
        {
            try
            {

                string tableName = string.Empty;
                string whereClause = string.Empty;
                AppParam.Instance.DBTableDict.TryGetValue(obj.GetType().Name, out tableName);
                string result = string.Format("SELECT {0} FROM {1} ", BuildSelectAllFieldCommand(fieldDict), tableName);

                Dictionary<string, object> conditionDict = BuildConditionDictionary(obj);
                if (conditionDict.Count == 0)
                {
                    parameters = null;
                    return result;
                }

                string tempDbFieldName = string.Empty;
                List<string> fieldNames = new List<string>();
                foreach (var o in conditionDict)
                {
                    if (!fieldDict.ContainsKey(o.Key))
                    {
                        continue;
                    }
                    fieldDict.TryGetValue(o.Key, out tempDbFieldName);
                    whereClause += string.Format("[{0}] = @{1} AND ", tempDbFieldName, o.Key);
                    fieldNames.Add(o.Key);
                }

                parameters = new OleDbParameter[fieldNames.Count];
                object tempValue;
                for (int i = 0; i < fieldNames.Count; i++)
                {
                    tempValue = obj.GetType().GetProperty(fieldNames[i]).GetValue(obj, null);
                    parameters[i] = new OleDbParameter(string.Format(@"{0}", fieldNames[i]), tempValue);
                }

                whereClause = whereClause.Trim().TrimEnd('D').TrimEnd('N').TrimEnd('A').TrimEnd();

                result += string.Format(" WHERE {0}", whereClause);

                return result;
            }
            catch (Exception ex) { throw; }
        }

        public static string BuildSelectCommand<T>(T obj, Dictionary<string, string> fieldDict,Dictionary<string, string> conditionDict, out OleDbParameter[] parameters)
        {
            try
            {

                string tableName = string.Empty;
                string whereClause = string.Empty;
                AppParam.Instance.DBTableDict.TryGetValue(obj.GetType().Name, out tableName);
                string result = string.Format("SELECT {0} FROM {1} ", BuildSelectAllFieldCommand(fieldDict), tableName);

                string tempDbFieldName = string.Empty;
                List<string> fieldNames = new List<string>();
                foreach (var o in conditionDict)
                {
                    if (!fieldDict.ContainsKey(o.Key))
                    {
                        continue;
                    }
                    fieldDict.TryGetValue(o.Key, out tempDbFieldName);
                    whereClause += string.Format("[{0}] = @{1} AND ", tempDbFieldName, o.Key);
                    fieldNames.Add(o.Key);
                }

                parameters = new OleDbParameter[fieldNames.Count];
                object tempValue;
                for (int i = 0; i < fieldNames.Count; i++)
                {
                    tempValue = obj.GetType().GetProperty(fieldNames[i]).GetValue(obj, null);
                    parameters[i] = new OleDbParameter(string.Format(@"{0}", fieldNames[i]), tempValue);
                }

                whereClause = whereClause.Trim().TrimEnd('D').TrimEnd('N').TrimEnd('A').TrimEnd();

                result += string.Format(" WHERE {0}", whereClause);

                return result;
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="obj"></param>
        /// <param name="dictionary"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static string BuildUpdatePartialCommand<T>(T obj, Dictionary<string, string> dictionary, Dictionary<string, string> conditionDict, out OleDbParameter[] parameters)
        {
            string result = string.Empty;
            string setClause = string.Empty;
            string whereClause = string.Empty;
            string tableName = string.Empty;
            AppParam.Instance.DBTableDict.TryGetValue(obj.GetType().Name, out tableName);

            Dictionary<string, object> updateDict = BuildConditionDictionary(obj, false);
            string tempDbFieldName = string.Empty;
            List<string> fieldNames = new List<string>();
            foreach (var o in updateDict)
            {
                if (!dictionary.ContainsKey(o.Key))
                {
                    continue;
                }
                dictionary.TryGetValue(o.Key, out tempDbFieldName);
                setClause += string.Format("[{0}] = @{1}, ", tempDbFieldName, o.Key);
                fieldNames.Add(o.Key);
            }

            foreach (var condition in conditionDict)
            {
                whereClause += string.Format("[{0}] = @{1} AND ", condition.Value, condition.Key);
                fieldNames.Add(condition.Key);
            }

            parameters = new OleDbParameter[fieldNames.Count];
            object tempValue;
            for (int i = 0; i < fieldNames.Count; i++)
            {
                tempValue = obj.GetType().GetProperty(fieldNames[i]).GetValue(obj, null);
                parameters[i] = new OleDbParameter(string.Format(@"{0}", fieldNames[i]), tempValue);
            }

            whereClause = whereClause.Trim().TrimEnd('D').TrimEnd('N').TrimEnd('A').TrimEnd();
            setClause = setClause.Trim().TrimEnd(',').Trim();
            result = string.Format("UPDATE [{0}] SET {1} WHERE {2}", tableName, setClause, whereClause);
            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="obj"></param>
        /// <param name="dictionary"></param>
        /// <param name="conditionDict"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static string BuildUpdateCommand<T>(T obj, Dictionary<string, string> dictionary, Dictionary<string, string> conditionDict, out OleDbParameter[] parameters)
        {
            string result = string.Empty;
            string setClause = string.Empty;
            string whereClause = string.Empty;
            string tableName = string.Empty;
            AppParam.Instance.DBTableDict.TryGetValue(obj.GetType().Name, out tableName);

            string tempDbFieldName = string.Empty;
            List<string> fieldNames = new List<string>();
            foreach (var o in dictionary)
            {
                if (dictionary.TryGetValue(o.Key, out tempDbFieldName))
                {
                    setClause += string.Format("[{0}] = @{1}, ", tempDbFieldName, o.Key);
                    fieldNames.Add(o.Key);
                }
            }

            foreach (var condition in conditionDict)
            {
                whereClause += string.Format("[{0}] = @{1} AND ", condition.Value, condition.Key);
                fieldNames.Add(condition.Key);
            }

            parameters = new OleDbParameter[fieldNames.Count];
            object tempValue;
            for (int i = 0; i < fieldNames.Count; i++)
            {
                tempValue = obj.GetType().GetProperty(fieldNames[i]).GetValue(obj, null);
                parameters[i] = new OleDbParameter(string.Format(@"{0}", fieldNames[i]), (tempValue ?? DBNull.Value));
            }

            whereClause = whereClause.Trim().TrimEnd('D').TrimEnd('N').TrimEnd('A').TrimEnd();
            setClause = setClause.Trim().TrimEnd(',').Trim();
            result = string.Format("UPDATE [{0}] SET {1} WHERE {2}", tableName, setClause, whereClause);
            return result;
        }

        /// <summary>
        /// Build Select All Query.
        /// </summary>
        /// <param name="tableName">Table To Select data.</param>
        /// <param name="fieldDict">A Dictionary of Field to query(Key: FieldName, Value: DB FieldName).</param>
        /// <returns></returns>
        public static string BuildSelectAllCommand(string tableName, Dictionary<string, string> fieldDict)
        {
            string result = string.Empty;
            result = string.Format("SELECT {0} FROM {1}", BuildSelectAllFieldCommand(fieldDict), tableName);

            return result;
        }

        public static string BuildDeleteCommand<T>(T obj, Dictionary<string, string> dictionary, out OleDbParameter[] parameters)
        {
            string result = string.Empty;
            string tableName = string.Empty;
            string whereClause = string.Empty;

            AppParam.Instance.DBTableDict.TryGetValue(obj.GetType().Name, out tableName);
            Dictionary<string, object> conditionDict = BuildConditionDictionary(obj);
            string dbFieldName = string.Empty;
            List<string> fieldNames = new List<string>();
            foreach (var o in conditionDict)
            {
                if (dictionary.TryGetValue(o.Key, out dbFieldName))
                {
                    whereClause += string.Format("[{0}] = @{1} AND ", dbFieldName, o.Key);
                    fieldNames.Add(string.Format(o.Key));
                }
            }

            parameters = new OleDbParameter[fieldNames.Count];
            object tempValue;
            for (int i = 0; i < fieldNames.Count; i++)
            {
                tempValue = obj.GetType().GetProperty(fieldNames[i]).GetValue(obj, null);
                parameters[i] = new OleDbParameter(string.Format("@{0}", fieldNames[i]), (tempValue ?? DBNull.Value));
            }
            whereClause = whereClause.Trim().TrimEnd('D').TrimEnd('N').TrimEnd('A').Trim();
            result = string.Format("DELETE FROM {0} WHERE {1}", tableName, whereClause);

            return result;
        }

        /// <summary>
        /// Build Where String (And condition only).
        /// Eg: [Filed1] = 5, [Field2] = 'ABC', [Field3] = #dd/mm/yyyy#
        /// </summary>
        /// <param name="template">Key: Object Prop Field Name, Value: Database Field Name.</param>
        /// <param name="dictionary">Key: Object Prop Field Name, Value: Value of Field.</param>
        /// <returns>String: [F1] = 1 AND [F2] = 'a' AND [F3] = #'dd/mm/yyyyy'#</returns>
        //public static string BuildWhereString(Dictionary<string, string> template, Dictionary<string, object> dictionary)
        //{
        //    try
        //    {
        //        string result = string.Empty;
        //        string key = string.Empty;
        //        foreach (var item in dictionary)
        //        {
        //            template.TryGetValue(item.Key, out key);
        //            result += string.Format("[{0}] = {1} AND ", key, BuildParamString(item.Value));
        //        }

        //        result = result.Trim().TrimEnd('D').TrimEnd('N').TrimEnd('A');
        //        return result;
        //    }
        //    catch (Exception ex)
        //    {
        //        throw;
        //    }
        //}

        public static string BuildWhereString<T>(T obj, Dictionary<string, string> template, out OleDbParameter[] parameters)
        {
            try
            {
                string result = string.Empty;
                List<string> fieldNames = new List<string>();

                Dictionary<string, object> conditionDict = BuildConditionDictionary(obj);

                string tempDbFieldName = string.Empty;
                foreach (var item in conditionDict)
                {
                    if (!template.TryGetValue(item.Key, out tempDbFieldName))
                    {
                        continue;
                    }
                    result += string.Format("[{0}] = @{1} AND ", tempDbFieldName, item.Key);
                    fieldNames.Add(item.Key);
                }

                parameters = new OleDbParameter[fieldNames.Count];
                object tempValue;
                for (int i = 0; i < fieldNames.Count; i++)
                {
                    tempValue = obj.GetType().GetProperty(fieldNames[i]).GetValue(obj, null);
                    parameters[i] = new OleDbParameter(string.Format("@{0}", fieldNames[i]), (tempValue ?? DBNull.Value));
                }
                result = result.Trim().TrimEnd('D').TrimEnd('N').TrimEnd('A');
                return result;
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        /// <summary>
        /// Build param value to SQL String for MS Access Database. 
        /// </summary>
        /// <param name="field">field to build.</param>
        /// <returns>field value in string.</returns>
        private static string BuildParamString(object field)
        {
            string strParam = field.ToString();

            switch (field.GetType().ToString().ToUpper())
            {
                case "SYSTEM.INT32":
                    strParam = string.Format("{0}", field);
                    break;
                case "SYSTEM.INT64":
                    strParam = string.Format("{0}", field);
                    break;
                case "SYSTEM.DECIMAL":
                    strParam = string.Format("{0}", field);
                    break;
                case "SYSTEM.STRING":
                    strParam = string.Format("'{0}'", field);
                    break;
                case "SYSTEM.DATETIME":
                    strParam = string.Format("#{0}#", field);
                    break;
                default:
                    strParam = string.Format("'{0}'", field);
                    break;
            }

            return strParam;
        }

        /// <summary>
        /// Build Condition Dictionary of object. Extract Field has value.
        /// To Build Where String.
        /// Key: Object Field Name.
        /// Value: value of field.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="obj">Object.</param>
        /// <returns></returns>
        public static Dictionary<string, object> BuildConditionDictionary<T>(T obj, bool excludeZero = true)
        {
            try
            {
                Type type = typeof(T);
                PropertyInfo[] fields = type.GetProperties();

                Dictionary<string, object> dictionary = new Dictionary<string, object>();
                object tempValue;
                foreach (var item in fields)
                {
                    tempValue = item.GetValue(obj, null);
                    if (tempValue == null)
                    {
                        continue;
                    }
                    if (excludeZero)
                    {
                        if ((tempValue is int ||
                            tempValue is long ||
                            tempValue is decimal) &&
                            tempValue.ToString() == "0")
                        {
                            continue;
                        }
                    }
                    dictionary.Add(item.Name, tempValue);
                }

                return dictionary;
            }
            catch (Exception)
            {
                throw;
            }
        }

    }
}
